{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ut_staff = pd.read_sql_table('ut_staff', engine)\n",
    "ut_student = pd.read_sql_table('ut_student', engine)\n",
    "ut_event = pd.read_sql_table('ut_event', engine)\n",
    "ut_room = pd.read_sql_table('ut_room', engine)\n",
    "ut_attends = pd.read_sql_table('ut_attends', engine)\n",
    "ut_teaches = pd.read_sql_table('ut_teaches', engine)\n",
    "ut_occurs = pd.read_sql_table('ut_occurs', engine)\n",
    "ut_modle = pd.read_sql_table('ut_modle', engine)\n",
    "ut_week = pd.read_sql_table('ut_week', engine)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**co.CHt is to be given all the teaching that co.ACg currently does. Identify those events which will clash.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>co.CHt</th>\n",
       "      <th>co.ACg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>co12005.T03</td>\n",
       "      <td>co12005.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>co12005.T03</td>\n",
       "      <td>co72013.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>co12005.T03</td>\n",
       "      <td>co72013.T03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        co.CHt       co.ACg\n",
       "0  co12005.T03  co12005.T01\n",
       "2  co12005.T03  co72013.L02\n",
       "3  co12005.T03  co72013.T03"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event.rename(columns={'id': 'evt_id'})\n",
    "     .merge(ut_teaches, left_on='evt_id', right_on='event')\n",
    "     .merge(ut_occurs, left_on='evt_id', right_on='event'))\n",
    "t['begin'] = pd.to_datetime(t['tod'], format='%H:%M')\n",
    "t['end'] = t['begin'] + pd.to_timedelta(t['duration'], unit='hours')\n",
    "\n",
    "a = (t.loc[t['staff']=='co.CHt'].merge(t.loc[t['staff']=='co.ACg'], \n",
    "                                       on=['week', 'dow']))\n",
    "(a.loc[((a['begin_x']>=a['begin_y']) & (a['begin_x']<a['end_y'])) |\n",
    "       ((a['begin_y']>=a['begin_x']) & (a['begin_y']<a['end_x'])),\n",
    "      ['evt_id_x', 'evt_id_y']].drop_duplicates()\n",
    " .rename(columns={'evt_id_x': 'co.CHt', 'evt_id_y': 'co.ACg'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**Produce a table showing the utilisation rate and the occupancy level for all rooms with a capacity more than 60.**\n",
    "\n",
    "> I don't know how 'utilisation rate' and 'occupancy level' are defined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room</th>\n",
       "      <th>util</th>\n",
       "      <th>occup_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cr.SMH</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.274574</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     room  util  occup_level\n",
       "0  cr.SMH  0.24     0.274574"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# theoretical full utilisation hours: 15 weeks * 5 days * 12 hours\n",
    "close_time = (max(pd.to_datetime(ut_event['tod'], format='%H:%M') + \n",
    "                  pd.to_timedelta(ut_event['duration'], unit='hours')))\n",
    "open_time = min(pd.to_datetime(ut_event['tod'], format='%H:%M'))\n",
    "max_hrs = (close_time - open_time).seconds // 3600\n",
    "t = ut_week.assign(max_hrs=5 * max_hrs)\n",
    "\n",
    "# attended students per event\n",
    "s = (ut_event.rename(columns={'id': 'event'})\n",
    "     .merge(ut_occurs, on='event')\n",
    "     .merge(ut_attends, on='event')\n",
    "     .merge(ut_student, left_on='student', right_on='id')\n",
    "     .merge(ut_room, left_on='room', right_on='id'))\n",
    "s = s.groupby('room')[['sze', 'capacity']].sum().reset_index()\n",
    "\n",
    "a = (ut_event.rename(columns={'id': 'event'}).merge(ut_occurs, on='event')\n",
    "     .merge(ut_room.loc[ut_room['capacity']>60], left_on='room', right_on='id')\n",
    "     .groupby('room')['duration']\n",
    "     .sum().reset_index()\n",
    "     .merge(s, on='room'))\n",
    "a['util'] = a['duration'] / sum(t['max_hrs'])\n",
    "a['occup_level'] = a['sze'] / a['capacity']\n",
    "a[['room', 'util', 'occup_level']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**A one hour staff meeting is to be held between 09:00 and 17:00. Events which clash are to be cancelled. Identify the hour which will result in the least disruption.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week</th>\n",
       "      <th>01</th>\n",
       "      <th>02</th>\n",
       "      <th>03</th>\n",
       "      <th>04</th>\n",
       "      <th>05</th>\n",
       "      <th>06</th>\n",
       "      <th>07</th>\n",
       "      <th>08</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">0</th>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">1</th>\n",
       "      <th>9</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">2</th>\n",
       "      <th>9</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">3</th>\n",
       "      <th>9</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"9\" valign=\"top\">4</th>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "week          01  02  03  04  05  06  07  08  10  11  12  13\n",
       "weekday hour                                                \n",
       "0       9      4   4   4   4   4   4   4   4   4   4   4   4\n",
       "        10     1   1   1   1   1   1   1   1   1   1   1   1\n",
       "        11     8   8   8   8   8   8   8   8   8   8   8   8\n",
       "        12     3   3   3   3   3   3   3   3   3   3   3   3\n",
       "        13    10  10  10  10  10  10  10  10  10  10  10  10\n",
       "        14     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        15    10  10  10  10  10  10  10  10  10  10  10  10\n",
       "        16     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        17     8   8   8   8   8   8   8   8   8   8   8   8\n",
       "        18     1   1   1   1   1   1   1   1   1   1   1   1\n",
       "1       9      6   6   6   6   6   6   6   6   6   6   6   6\n",
       "        10     7   7   7   7   7   7   7   7   7   7   7   7\n",
       "        11    10  10  10  10  10  10  10  10  10  10  10  10\n",
       "        12    10  10  10  10  10  10  10  10  10  10  10  10\n",
       "        13    11  11  11  11  11  11  11  11  11  11  11  11\n",
       "        14     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        15    10  10  10  10  10  10  10  10  10  10  10  10\n",
       "        16     8   8   8   8   8   8   8   8   8   8   8   8\n",
       "        17     4   4   4   4   4   4   4   4   4   4   4   4\n",
       "        18     4   4   4   4   4   4   4   4   4   4   4   4\n",
       "        20     4   4   4   4   4   4   4   4   4   4   4   4\n",
       "2       9      8   8   8   8   8   8   8   8   8   8   8   8\n",
       "        10     3   3   3   3   3   3   3   3   3   3   3   3\n",
       "        11     8   8   8   8   8   8   8   8   8   8   8   8\n",
       "        12     7   7   7   7   7   7   7   7   7   7   7   7\n",
       "        13     6   6   6   6   6   6   6   6   6   6   6   6\n",
       "        14     6   6   6   6   6   6   6   6   6   6   6   6\n",
       "        15     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        16     7   7   7   7   7   7   7   7   7   7   7   7\n",
       "        17     3   3   3   3   3   3   3   3   3   3   3   3\n",
       "        18     2   2   2   2   2   2   2   2   2   2   2   2\n",
       "3       9      4   4   4   4   4   4   4   4   4   4   4   4\n",
       "        10     4   4   4   4   4   4   4   4   4   4   4   4\n",
       "        11    11  11  11  11  11  11  11  11  11  11  11  11\n",
       "        12     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        13     9   9   9   9   9   9   9   9   9   9   9   9\n",
       "        14     7   7   7   7   7   7   7   7   7   7   7   7\n",
       "        15    11  11  11  11  11  11  11  11  10  10  10  10\n",
       "        16     6   6   6   6   6   6   6   6   6   6   6   6\n",
       "        17     8   8   8   8   8   8   8   8   7   7   7   7\n",
       "        18     1   1   1   1   1   1   1   1   1   1   1   1\n",
       "4       9      5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        10     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        11     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        12     5   5   5   5   5   5   5   5   5   5   5   5\n",
       "        13     7   7   7   6   6   6   6   6   6   6   6   6\n",
       "        14     3   3   3   3   3   3   3   3   4   4   4   4\n",
       "        15     7   7   7   7   7   7   7   7   7   7   7   7\n",
       "        16     4   4   4   4   3   3   3   3   4   4   4   4\n",
       "        17     6   6   6   6   6   6   6   6   6   6   6   6"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event.assign(tod=pd.to_datetime(ut_event['tod'], format='%H:%M'),\n",
    "                     duration=ut_event['duration'].astype(int))\n",
    "     .merge(ut_occurs, left_on='id', right_on='event')\n",
    "     [['event', 'week', 'dow', 'tod', 'duration']])\n",
    "for i in range(2, 10):\n",
    "    t_ = t.loc[t['duration']==i]\n",
    "    if t_.shape[0] > 0:\n",
    "        t_.loc[:, 'tod'] = t_.loc[:, 'tod'] + pd.to_timedelta(i, unit='hours')\n",
    "        t = pd.concat([t, t_])\n",
    "        \n",
    "(t.assign(weekday=t['dow'].replace({'Monday': 0, 'Tuesday': 1, 'Wednesday': 2,\n",
    "                                    'Thursday': 3, 'Friday': 4}),\n",
    "          hour=t['tod'].dt.hour)\n",
    " .groupby(['week', 'weekday', 'hour'])['event']\n",
    " .count().reset_index()\n",
    " .pivot(index=['weekday', 'hour'], columns='week', values='event'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tod</th>\n",
       "      <th>dow</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10:00:00</td>\n",
       "      <td>Monday</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        tod     dow  event\n",
       "6  10:00:00  Monday     12"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You may find that week '09', '14', '15' are totally vacant\n",
    "# or you can choose Mon 9am/12pm, Tue 5pm, Thu 9am, Fri 5pm\n",
    "\n",
    "(t.assign(tod=t['tod'].dt.time)\n",
    " .loc[t['tod'].dt.hour.between(9, 17)]\n",
    " .groupby(['tod', 'dow'])['event']\n",
    " .count().reset_index().sort_values(['event', 'tod', 'dow'])\n",
    " .iloc[:1, :])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Find all clashes - include the events which clash and the staff, student or rooms that they have in common.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>event_x</th>\n",
       "      <th>event_y</th>\n",
       "      <th>room</th>\n",
       "      <th>week</th>\n",
       "      <th>dow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3304</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>01</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3319</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>10</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3328</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>11</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3337</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>12</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3346</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>13</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3356</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>02</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3372</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>03</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3387</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>04</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3396</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>05</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3405</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>06</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3414</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>07</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3423</th>\n",
       "      <td>co22020.T04</td>\n",
       "      <td>co22007.T01</td>\n",
       "      <td>co.LB47</td>\n",
       "      <td>08</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3791</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>01</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3795</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3799</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>13</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3811</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>02</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3815</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>03</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3819</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>04</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3823</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>05</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3827</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3831</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>07</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3835</th>\n",
       "      <td>co22020.T03</td>\n",
       "      <td>co22008.T01</td>\n",
       "      <td>None</td>\n",
       "      <td>08</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3839</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>01</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3843</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>10</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3847</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3851</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3855</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>13</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3859</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>02</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3863</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>03</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3867</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>04</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3871</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>05</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3875</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>06</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3879</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>07</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3883</th>\n",
       "      <td>coh2451.T01</td>\n",
       "      <td>co22008.T03</td>\n",
       "      <td>None</td>\n",
       "      <td>08</td>\n",
       "      <td>Wednesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3897</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>01</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3913</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>10</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3929</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>11</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3945</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>12</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3961</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>13</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3977</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>02</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3993</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>03</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4009</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>04</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4025</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>05</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4041</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>06</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4057</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>07</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4073</th>\n",
       "      <td>coh8412615.T03</td>\n",
       "      <td>co22008.T04</td>\n",
       "      <td>co.117+118</td>\n",
       "      <td>08</td>\n",
       "      <td>Tuesday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             event_x      event_y        room week        dow\n",
       "3304     co22020.T04  co22007.T01     co.LB47   01     Friday\n",
       "3319     co22020.T04  co22007.T01     co.LB47   10     Friday\n",
       "3328     co22020.T04  co22007.T01     co.LB47   11     Friday\n",
       "3337     co22020.T04  co22007.T01     co.LB47   12     Friday\n",
       "3346     co22020.T04  co22007.T01     co.LB47   13     Friday\n",
       "3356     co22020.T04  co22007.T01     co.LB47   02     Friday\n",
       "3372     co22020.T04  co22007.T01     co.LB47   03     Friday\n",
       "3387     co22020.T04  co22007.T01     co.LB47   04     Friday\n",
       "3396     co22020.T04  co22007.T01     co.LB47   05     Friday\n",
       "3405     co22020.T04  co22007.T01     co.LB47   06     Friday\n",
       "3414     co22020.T04  co22007.T01     co.LB47   07     Friday\n",
       "3423     co22020.T04  co22007.T01     co.LB47   08     Friday\n",
       "3791     co22020.T03  co22008.T01        None   01    Tuesday\n",
       "3795     co22020.T03  co22008.T01        None   10    Tuesday\n",
       "3799     co22020.T03  co22008.T01        None   11    Tuesday\n",
       "3803     co22020.T03  co22008.T01        None   12    Tuesday\n",
       "3807     co22020.T03  co22008.T01        None   13    Tuesday\n",
       "3811     co22020.T03  co22008.T01        None   02    Tuesday\n",
       "3815     co22020.T03  co22008.T01        None   03    Tuesday\n",
       "3819     co22020.T03  co22008.T01        None   04    Tuesday\n",
       "3823     co22020.T03  co22008.T01        None   05    Tuesday\n",
       "3827     co22020.T03  co22008.T01        None   06    Tuesday\n",
       "3831     co22020.T03  co22008.T01        None   07    Tuesday\n",
       "3835     co22020.T03  co22008.T01        None   08    Tuesday\n",
       "3839     coh2451.T01  co22008.T03        None   01  Wednesday\n",
       "3843     coh2451.T01  co22008.T03        None   10  Wednesday\n",
       "3847     coh2451.T01  co22008.T03        None   11  Wednesday\n",
       "3851     coh2451.T01  co22008.T03        None   12  Wednesday\n",
       "3855     coh2451.T01  co22008.T03        None   13  Wednesday\n",
       "3859     coh2451.T01  co22008.T03        None   02  Wednesday\n",
       "3863     coh2451.T01  co22008.T03        None   03  Wednesday\n",
       "3867     coh2451.T01  co22008.T03        None   04  Wednesday\n",
       "3871     coh2451.T01  co22008.T03        None   05  Wednesday\n",
       "3875     coh2451.T01  co22008.T03        None   06  Wednesday\n",
       "3879     coh2451.T01  co22008.T03        None   07  Wednesday\n",
       "3883     coh2451.T01  co22008.T03        None   08  Wednesday\n",
       "3897  coh8412615.T03  co22008.T04  co.117+118   01    Tuesday\n",
       "3913  coh8412615.T03  co22008.T04  co.117+118   10    Tuesday\n",
       "3929  coh8412615.T03  co22008.T04  co.117+118   11    Tuesday\n",
       "3945  coh8412615.T03  co22008.T04  co.117+118   12    Tuesday\n",
       "3961  coh8412615.T03  co22008.T04  co.117+118   13    Tuesday\n",
       "3977  coh8412615.T03  co22008.T04  co.117+118   02    Tuesday\n",
       "3993  coh8412615.T03  co22008.T04  co.117+118   03    Tuesday\n",
       "4009  coh8412615.T03  co22008.T04  co.117+118   04    Tuesday\n",
       "4025  coh8412615.T03  co22008.T04  co.117+118   05    Tuesday\n",
       "4041  coh8412615.T03  co22008.T04  co.117+118   06    Tuesday\n",
       "4057  coh8412615.T03  co22008.T04  co.117+118   07    Tuesday\n",
       "4073  coh8412615.T03  co22008.T04  co.117+118   08    Tuesday"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event.assign(start=pd.to_datetime(ut_event['tod'], format='%H:%M'))\n",
    "    .rename(columns={'id': 'event'})\n",
    "    .merge(ut_occurs, on='event'))\n",
    "\n",
    "t['end'] = t['start'] + pd.to_timedelta(t['duration'], unit='hours')\n",
    "\n",
    "a = t.merge(t, on=['week', 'dow', 'room']).query('event_x != event_y')\n",
    "\n",
    "a = a.loc[(((a['start_x'] >= a['start_y']) & (a['start_x'] < a['end_y'])) |\n",
    "           ((a['start_y'] >= a['start_x']) & (a['start_y'] < a['end_x']))) &\n",
    "          (a['event_x'] > a['event_y']),\n",
    "         ['event_x', 'event_y', 'room', 'week', 'dow']].drop_duplicates()\n",
    "a"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Produce a timetable for a group of full time students for week 1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>dow</th>\n",
       "      <th colspan=\"9\" halign=\"left\">Friday</th>\n",
       "      <th>Monday</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">Wednesday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tod</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>student</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>co.12008.Ea</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>co.12008.Eb</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>co.12012.E</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>co.22022.E</th>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>co1.BAe</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>com.ST</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>com.ST.a</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>com.ST.b</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>com.ST.th</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>com.ST.tu</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>86 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "dow         Friday                         Monday  ... Wednesday              \\\n",
       "tod             9  10 11 12 13 14 15 16 17     9   ...        9  10 11 12 13   \n",
       "student                                            ...                         \n",
       "co.12008.Ea                                        ...                         \n",
       "co.12008.Eb                     Y     Y            ...                         \n",
       "co.12012.E                                         ...                  Y  Y   \n",
       "co.22022.E       Y  Y     Y                        ...                         \n",
       "co1.BAe                                         Y  ...         Y     Y         \n",
       "...            ... .. .. .. .. .. .. .. ..    ...  ...       ... .. .. .. ..   \n",
       "com.ST                                             ...                         \n",
       "com.ST.a                                           ...                         \n",
       "com.ST.b                                           ...                         \n",
       "com.ST.th                                          ...                         \n",
       "com.ST.tu                                          ...                         \n",
       "\n",
       "dow                         \n",
       "tod         14 15 16 17 18  \n",
       "student                     \n",
       "co.12008.Ea                 \n",
       "co.12008.Eb                 \n",
       "co.12012.E      Y           \n",
       "co.22022.E                  \n",
       "co1.BAe                     \n",
       "...         .. .. .. .. ..  \n",
       "com.ST       Y     Y        \n",
       "com.ST.a                    \n",
       "com.ST.b                    \n",
       "com.ST.th                   \n",
       "com.ST.tu                   \n",
       "\n",
       "[86 rows x 50 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (ut_event.assign(tod=pd.to_datetime(ut_event['tod'], format='%H:%M'))\n",
    "     .rename(columns={'id': 'event'})\n",
    "     .merge(ut_occurs.loc[ut_occurs['week']=='01'], on='event')\n",
    "     .merge(ut_attends, on='event'))\n",
    "for i in range(2, 10):\n",
    "    t_ = t.loc[t['duration']==i]\n",
    "    if t_.shape[0] > 0:\n",
    "        t_.loc[:, 'tod'] = t_.loc[:, 'tod'] + pd.to_timedelta(i, unit='hours')\n",
    "        t = pd.concat([t, t_])\n",
    "\n",
    "t['tod'] = t['tod'].dt.hour\n",
    "t['flag'] = 'Y'\n",
    "(t[['student', 'tod', 'dow', 'flag']]\n",
    " .sort_values(['dow', 'tod'])\n",
    " .drop_duplicates()\n",
    " .pivot(index='student', columns=['dow', 'tod'], values='flag')\n",
    " .fillna(''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
